import sqlite3

def connect_db():
    try:
        conn = sqlite3.connect('trade_monitor.db')
        return conn
    except Exception as e:
        print(f"数据库连接失败: {e}")
        from PyQt5.QtWidgets import QMessageBox
        QMessageBox.critical(None, "错误", f"数据库连接失败: {e}")
        return None

#交易品种管理***********************************************
def get_all_products(conn):
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM products order by is_monitored DESC")
    return cursor.fetchall()

def insert_product(conn, data):
    cursor = conn.cursor()
    cursor.execute("INSERT INTO products (name, exchange_code, margin, stoploss, takeprofit, limit_stoploss, mtp,dks,is_monitored) VALUES (?,?,?,?,?,?,?,?,?)", data)

def update_product(conn, product_id, data):
    update_query = "UPDATE products SET "
    update_query += ", ".join([f"{key} = ?" for key in data.keys()])
    update_query += " WHERE id = ?"
    values = list(data.values()) + [product_id]
    cursor = conn.cursor()
    cursor.execute(update_query, values)
    conn.commit()

def delete_product(conn, product_id):
    cursor = conn.cursor()
    cursor.execute("DELETE FROM products WHERE id = ?", (product_id,))

def get_monitored_products(conn):
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM products WHERE is_monitored = 1")
    return cursor.fetchall()

def delete_product_by_name(conn, product_name):
    cursor = conn.cursor()
    cursor.execute("DELETE FROM products WHERE name = ?", (product_name,))

def get_product_by_id(conn, product_id):
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM products WHERE id = ?", (product_id,))
    return cursor.fetchone()
    
#策略管理页面需要用到的数据库操作函数
def get_all_strategies(conn):
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM products")
    return cursor.fetchall()

def update_strategy(conn, strategy_id, updates):
    """更新策略数据"""
    if not conn:
        raise ValueError("数据库连接无效")
    if not strategy_id:
        raise ValueError("策略ID不能为空")
    
    columns = list(updates.keys())  # 使用传入的键作为列名
    set_clause = ", ".join([f"{col} = ?" for col in columns])
    values = list(updates.values()) + [strategy_id]
    
    cursor = conn.cursor()
    try:
        # 验证策略是否存在
        cursor.execute("SELECT id FROM products WHERE id = ?", (strategy_id,))
        if not cursor.fetchone():
            raise ValueError(f"策略ID {strategy_id} 不存在")
            
        # 执行更新
        cursor.execute(f"UPDATE products SET {set_clause} WHERE id = ?", values)
        conn.commit()
    except Exception as e:
        conn.rollback()
        raise e

#交易记录管理********************************
def insert_trade_record(conn, record):
    """添加交易记录"""
    sql = '''INSERT INTO trade_records(
                product_name, exchange_code,
                direction, action_type, volume, 
                price, remark)
             VALUES(?,?,?,?,?,?,?)'''
    cur = conn.cursor()
    try:
        cur.execute(sql, record)
        conn.commit()
        return cur.lastrowid
    except Error as e:
        print(f"添加交易记录错误: {e}")
        return None

def get_trade_records(conn, product_name=None, limit=100):
    """获取交易记录"""
    cur = conn.cursor()
    try:
        if product_name:
            cur.execute('''SELECT * FROM trade_records 
                        WHERE product_name=? 
                        ORDER BY trade_time DESC LIMIT ?''', 
                       (product_name, limit))
        else:
            cur.execute('''SELECT * FROM trade_records 
                        ORDER BY trade_time DESC LIMIT ?''', (limit,))
        return cur.fetchall()
    except Error as e:
        print(f"获取交易记录错误: {e}")
        return None

if __name__ == "__main__":
    conn = connect_db()
    if conn:
        test=get_monitored_products(conn)
        print(test)

